/**
 * 公用DAO
 */
package cn.antia.db;

import java.util.*;
import java.sql.*;

/**
 * User: Jair Shaw
 * Date: 2008-6-27
 * Time: 16:39:48
 */
public class CommonDAO{
    public static SQLGene sqlGene;

    public static SQLGene getSqlGene() {
        return sqlGene;
    }
    /**
     * 查询，指定页与页大小
     * @param sql 原始SQL
     * @param page 页码
     * @param pageSize 页大小
     * @param orderField 排序字段
     * @param isAsc 是否升序
     * @param clazz 返回的对象名称
     * @return List<T>
     */
    public static <T> List<T> queryList(String sql,int page,int pageSize,String orderField,boolean isAsc,Class<T> clazz){
		sql = sqlGene.composeSqlPage(sql, orderField, isAsc, page, pageSize);
		return queryList(sql,clazz);
	}
    /**
     * 查询，指定页与页大小
     * @param sqlParams 原始SQL
     * @param page 页码
     * @param pageSize 页大小
     * @param orderField 排序字段
     * @param isAsc 是否升序
     * @param clazz 返回的对象名称
     * @return List<T>
     */
    public static <T> List<T> queryList(SqlParams sqlParams,int page,int pageSize,String orderField,boolean isAsc,Class<T> clazz){
        sqlParams.setSql(sqlGene.composeSqlPage(sqlParams.getSql(), orderField, isAsc, page, pageSize));
        return queryList(sqlParams,clazz);
	}
    /**
     * 查询语句，返回JavaBean
     * JavaBean的属性只支持基本的JDBC SQL类型
     * @param <T> <T>
     * @param sql sql
     * @param clazz 返回的对象类型
     * @return  List<T>
     */
    public static <T> List<T> queryList(String sql,Class<T> clazz) {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            return BeanProcessor.toBeanList(rs,clazz);
        } catch (SQLException e) {
            rethrow(e,sql);
            return null;
        } finally {
            DbUtils.closeQuietly(conn,statement,rs);
        }
    }
    /**
     * 查询，返回一个对象，对象由参数clazz决定
     * @param  sqlParams:JdbcSql
     * @param clazz Class
     * @return List<T>
     */
    public static <T> List<T> queryList(SqlParams sqlParams,Class<T> clazz) {
    	Connection conn = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            statement = getPreparedStatement(conn, statement, sqlParams);
            rs = statement.executeQuery();
            return BeanProcessor.toBeanList(rs,clazz);
        } catch (SQLException e) {
            rethrow(e,sqlParams);
            return null;
        } finally {
            DbUtils.closeQuietly(conn,statement,rs);
        }
    }

    /**
     * 查询这条SQL语句的记录总数
     * @param sql sql
     * @return 记录总数
     */
    public static int getRecordCount(String sql) {
    	sql = sqlGene.getRecordCountSQL(sql);
        Long i = queryFirstObject(sql);
        return i.intValue();
    }
    /**
     * 查询这条SQL语句的记录总数
     * @param sqlParams JdbcSql
     * @return 记录总数
     */
    public static int getRecordCount(SqlParams sqlParams) {
    	sqlParams.setSql(sqlGene.getRecordCountSQL(sqlParams.getSql()));
        Object i = queryFirstObject(sqlParams);
        return i == null ? 0 : Integer.parseInt(i.toString());
    }

    /**
     * 返回第一列与第二列的MAP，以第一列为KEY，第二列为VALUE
     * @param sql sql,查询字段必须为2个或以上，否则将返回为空的MAP
     * @return Map<K, T>
     */
    @SuppressWarnings(value = {"unchecked"})
    public static <K, T> Map<K, T> queryMap(String sql) {
        Map<K, T> map = new HashMap<K, T>();
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            ResultSetMetaData metaData = rs.getMetaData();
            if (metaData.getColumnCount() < 2) {
                return map;
            }
            //以第一列为key,第二列为value
            while (rs.next()) {
                map.put((K) rs.getObject(1), (T) rs.getObject(2));
            }
        } catch (SQLException e) {
            rethrow(e,sql);
        } finally {
            DbUtils.closeQuietly(conn, statement, rs);
        }
        return map;
    }

    /**
     * 返回第一列与第二列的MAP，以第一列为KEY，第二列为VALUE
     * @param sqlParams JdbcSql,查询字段必须为2个或以上，否则将返回为空的MAP
     * @return Map<K, T>
     */
    @SuppressWarnings(value = {"unchecked"})
    public static <K,T> Map<K, T> queryMap(SqlParams sqlParams) {
        Map<K, T> map = new HashMap<K,T>();
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            statement = getPreparedStatement(conn, statement, sqlParams);
            rs = statement.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            if(metaData.getColumnCount() < 2){
                return map;
            }
            //以第一列为key,第二列为value
            while (rs.next()) {
                map.put((K)rs.getObject(1),(T)rs.getObject(2));
            }
        } catch (SQLException e) {
            rethrow(e,sqlParams);
        } finally {
            DbUtils.closeQuietly(conn,statement,rs);
        }
        return map;
    }

   /**
     * 执行查询单列结果的SQL，泛型的类型可以是Java - JDBC 映射的类型(如Date,String,Integer,Long......)
     * @param sql       sql
     * @return          list<T>
     */
    @SuppressWarnings(value = {"unchecked"})
    public static <T> List<T> queryFirstField(String sql){
        List<T> ts = new ArrayList<T>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()){
                ts.add((T)rs.getObject(1));
            }
        } catch (SQLException e) {
            rethrow(e,sql);
        }finally {
           DbUtils.closeQuietly(conn,stmt,rs);
        }
        return ts;
    }
    /**
     * 执行查询单列结果的SQL，泛型的类型可以是Java - JDBC 映射的类型(如Date,String,Integer,Long......)
     * @param sqlParams       sql
     * @return          list<T>
     */
    @SuppressWarnings(value = {"unchecked"})
    public static <T> List<T> queryFirstField(SqlParams sqlParams){
        List<T> ts = new ArrayList<T>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            stmt = getPreparedStatement(conn, stmt, sqlParams);
            rs = stmt.executeQuery();
            while (rs.next()){
                ts.add((T)rs.getObject(1));
            }
        } catch (SQLException e) {
            rethrow(e,sqlParams);
        }finally {
            DbUtils.closeQuietly(conn,stmt,rs);
        }
        return ts;
    }
    /**
     * 查询，指定页与页大小
     * @param sql 原始SQL
     * @param page 页码
     * @param pageSize 页大小
     * @param orderField 排序字段
     * @param isAsc 是否升序
     * @return List<T>
     */
    public static List<Map<String, Object>> queryListMap(String sql, int page, int pageSize, String orderField, boolean isAsc) {
        sql = sqlGene.composeSqlPage(sql, orderField, isAsc, page, pageSize);
        return queryListMap(sql);
    }
    /**
     * 查询，指定页与页大小
     * @param sqlParams 原始SQL
     * @param page 页码
     * @param pageSize 页大小
     * @param orderField 排序字段
     * @param isAsc 是否升序
     * @return List<T>
     */
    public static List<Map<String, Object>> queryListMap(SqlParams sqlParams, int page, int pageSize, String orderField, boolean isAsc) {
        sqlParams.setSql(sqlGene.composeSqlPage(sqlParams.getSql(), orderField, isAsc, page, pageSize));
        return queryListMap(sqlParams);
    }
    
    /**
     * 根据SQL,返回查询结果,List<Map<field,value>>
     * @param sql:SQL语句
     * @return list:(表信息列表，一条信息用一个Map代表)
     */
    public static List<Map<String,Object>> queryListMap(String sql) {
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            //取出记录
            while (rs.next()) {
                list.add(BeanProcessor.toMap(rs));
            }
        } catch (SQLException e) {
            rethrow(e,sql);
        } finally {
            DbUtils.closeQuietly(conn,statement,rs);
        }
        return list;
    }

     /**
     * 根据SQL,返回查询结果,List<Map<field,value>>
     * @param sqlParams:SQL语句
     * @return list:(表信息列表，一条信息用一个Map代表)
     */
    public static List<Map<String,Object>> queryListMap(SqlParams sqlParams) {
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            pstmt = getPreparedStatement(conn, pstmt, sqlParams);
            rs = pstmt.executeQuery();
           //取出记录
            while (rs.next()) {
                list.add(BeanProcessor.toMap(rs));
            }
        } catch (SQLException e) {
            rethrow(e,sqlParams);
        } finally {
            DbUtils.closeQuietly(conn,pstmt,rs);
        }
        return list;
    }

    /**
     * 查询第一个值 一般用于 select name from User where ID = 1 之类的情况
     * @param sql sql
     * @return <T>
     */
    @SuppressWarnings(value = {"unchecked"})
    public static <T> T queryFirstObject(String sql) {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            if(rs.next()) {
                return (T)rs.getObject(1);
            }
        } catch (SQLException e) {
            rethrow(e,sql);
        } finally {
            DbUtils.closeQuietly(conn,statement,rs);
        }
        return null;
    }
    /**
     * 查询第一个值 一般用于 select name from User where ID = 1 之类的情况
     * @param sqlParams sql
     * @return <T>
     */
    @SuppressWarnings(value = {"unchecked"})
    public static <T> T queryFirstObject(SqlParams sqlParams) {
        if (sqlParams.getParams() == null || sqlParams.getParams().length < 1) {
            return (T)queryFirstObject(sqlParams.getSql());
        }
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            statement = getPreparedStatement(conn, statement, sqlParams);
            rs = statement.executeQuery();
            if (rs.next()) {
                return (T) rs.getObject(1);
            }
        } catch (SQLException e) {
            rethrow(e,sqlParams);
        } finally {
            DbUtils.closeQuietly(conn, statement, rs);
        }
        return null;
    }

    private static PreparedStatement getPreparedStatement(Connection conn,PreparedStatement ps, SqlParams sqlParams)throws SQLException{
        ps = conn.prepareStatement(sqlParams.getSql());
        int i = 0;
        for (Object o : sqlParams.getParams()) {
            ps.setObject(++i, o);
        }
        return ps;
    }

    /**
     * 根据SQL,返回第一行查询结果,Map<field,value>
     *
     * @param sql:SQL语句
     * @return Map<field,value>:(表信息列表)
     */
    public static Map<String,Object> queryFirstMap(String sql){
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            //将结果放入Map中
            if (rs.next()) {
                return BeanProcessor.toMap(rs);
            }
        } catch (SQLException e) {
            rethrow(e,sql);
        } finally {
            DbUtils.closeQuietly(conn,statement,rs);
        }
        return null;
    }

    /**
     * 根据SQL,返回第一行查询结果,Map<field,value>
     *
     * @param sqlParams:SQL语句
     * @return Map<field,value>:(表信息列表)
     */
    public static Map<String,Object> queryFirstMap(SqlParams sqlParams){
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            statement = getPreparedStatement(conn, statement, sqlParams);
            rs = statement.executeQuery();
            if (rs.next()) {
                return BeanProcessor.toMap(rs);
            }
        } catch (SQLException e) {
            rethrow(e,sqlParams);
        } finally {
            DbUtils.closeQuietly(conn,statement,rs);
        }
        return null;
    }

    /**
     * 查询，将结果组合为一个Java Object
     * @param sql sql
     * @param clazz T
     * @return T
     */
    public static <T> T queryObject(String sql,Class<T> clazz){
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            if (rs.next()) {
                return BeanProcessor.toBean(rs,clazz);
            }
        } catch (SQLException e) {
            rethrow(e,sql);
        } finally {
            DbUtils.closeQuietly(conn,statement,rs);
        }
        return null;
    }
    /**
     * 查询，将结果组合为一个Java Object
     * @param sqlParams sql
     * @param clazz T
     * @return T
     */
    public static <T> T queryObject(SqlParams sqlParams,Class<T> clazz){
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            statement = getPreparedStatement(conn, statement, sqlParams);
            rs = statement.executeQuery();
            if (rs.next()) {
                return BeanProcessor.toBean(rs,clazz);
            }
        } catch (SQLException e) {
            rethrow(e,sqlParams);
        } finally {
            DbUtils.closeQuietly(conn,statement,rs);
        }
        return null;
    }

    /**
     * 运行SQL
     * @param sql sql statement
     * @return bool
     */
    public static boolean runSql(String sql) {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            statement = conn.createStatement();
            return statement.execute(sql);
        } catch (SQLException e) {
            rethrow(e,sql);
        }finally {
           DbUtils.closeQuietly(conn,statement,rs);
        }
        return false;
    }

    /**
	 * 更新
	 * @param sql	SQL
	 * @return	更新行数
	 */
    public static int update(String sql) {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            statement = conn.createStatement();
            return statement.executeUpdate(sql);
        } catch (SQLException e) {
            rethrow(e,sql);
            return 0;
        }finally {
           DbUtils.closeQuietly(conn,statement,rs);
        }
    }
     /**
	 * 更新
	 * @param sqlParams	SQL
	 * @return	更新行数
	 */
    public static int update(SqlParams sqlParams) {
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            statement = getPreparedStatement(conn, statement, sqlParams);
            return statement.executeUpdate();
        } catch (SQLException e) {
            rethrow(e,sqlParams);
            return 0;
        }finally {
           DbUtils.closeQuietly(conn,statement,rs);
        }
    }

    /**
         * Throws a new exception with a more informative error message.
         *
         * @param cause The original exception that will be chained to the new
         * exception when it's rethrown.
         *
         * @param sqlParams The query that was executing when the exception happened.
         *
         * valid value to pass in.
         *
         */
        public static  void rethrow(SQLException cause, SqlParams sqlParams){
            rethrow(cause,sqlParams.toString());
        }

        /**
         * Throws a new exception with a more informative error message.
         *
         * @param cause The original exception that will be chained to the new
         * exception when it's rethrown.
         *
         * @param sql query sql statement.
         *
         * valid value to pass in.
         *
         */
        public static  void rethrow(SQLException cause, String sql) {
            StringBuffer msg = new StringBuffer(cause.getMessage()).append(sql);
            SQLException e = new SQLException(msg.toString(), cause.getSQLState(),
                    cause.getErrorCode());
            e.setNextException(cause);
            throw new DbException(msg.toString(),e);
        }

}
